package sdfi.lee.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import sdfi.lee.Entity.Drug;
import sdfi.lee.Entity.Enterprise;

public class DrugDAO{
	public String drugAdd(Drug drug){
		
		Session session=null;
		Transaction ts=null;
		try
		{
			
			session=DAO.openSession();
			ts=session.beginTransaction();
			if(drug.getEnterpid()!=null)
			{
				Enterprise ep=(Enterprise)session.get(Enterprise.class, drug.getEnterpid());
				ep.addTodrugs(drug);
				drug.setEpid(ep);
				session.update(ep);
			}
			else
			{
				session.save(drug);
			}
			ts.commit();
			return "success";
		}
		catch(Exception ee)
		{
			System.out.println("Exception when drug added");
			ee.printStackTrace();
			ts.rollback();
			return "error";
		}
		finally
		{
			DAO.closeSession();
		}
	
	}
	public Drug getDrugById(Integer id)
	{
		Drug drug=null;
		Session	session=null;
		Transaction ts=null;
		try
		{
			session=DAO.openSession();
			ts=session.beginTransaction();
			drug=(Drug)session.load(Drug.class, id);
			ts.commit();
		}
		catch(Exception ee)
		{
			System.out.println("Exception ee when get drug by id");
			ts.rollback();
			ee.printStackTrace();
		}
		finally
		{
			DAO.openSession();
		}
		return drug;
	}
	public List<Drug> getAllDrug(int size,int epid,int page)
	{
		List<Drug> list=null;
		Session session=null;
		Transaction ts=null;
		try
		{
			session=DAO.openSession();
			ts=session.beginTransaction();
			Query query=null;
			System.out.println(epid);
			if(epid==0)
			{
				query=session.createQuery("select new sdfi.lee.Entity.Drug(d.id,d.drugname,d.drugdate) from Drug as d where d.epid=null");
			}
			else 
			{
				query=session.createQuery(" select new sdfi.lee.Entity.Drug(d.id,d.drugname,d.drugdate) from Drug as d where d.epid=:epid");
				query.setInteger("epid", epid);
			}
			query.setFirstResult((page-1)*size);
			query.setMaxResults(size);
			list=query.list();
			//System.out.println(list.size());
			ts.commit();
			DAO.closeSession();
		}
		catch(Exception ee)
		{
			System.out.println("Exception ee when you get all Drug");
			ee.printStackTrace();
			ts.rollback();
		}
		finally
		{
			DAO.closeSession();
		}
		return list;
	}

	public String drugDelete(Integer id) throws Exception
	{
		Session session=DAO.openSession();
		Connection con=null;
		try
		{
			con=session.connection();
			Statement sts=con.createStatement();
			String sql="delete from drug where id='"+id+"'";
			boolean b=sts.execute(sql);
			con.commit();
			return "success";
		
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			con.rollback();
			return "error";
		}
		finally
		{
			DAO.closeSession();
		}
	
	}
	public String drugModify(Drug drug)throws Exception
	{
		Session session=null;
		Connection con=null;
		try
		{
			session=DAO.openSession();
			con=session.connection();
			String sql="update drug set ";
			sql+="drugname='"+drug.getDrugname()+"',";
			sql+="drupdescribe='"+drug.getDrupdescribe()+"' ";
			sql+="where id='"+drug.getId()+"'";
			Statement sts=con.createStatement();
			sts.execute(sql);
			
			con.commit();
			return "success";
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			con.rollback();
			return "error";
		}
		finally
		{
			DAO.closeSession();
		}
		
	}

	public List<Drug> getAllDrugAndCrawlDrug(int size,int epid,int page)
	{
		List<Drug> list=new ArrayList<Drug>();
		try
		{
			String sql="select id, title,dtype,content,ddate from(";
			sql+="select id as id,drugname as title,1 as dtype,'hehhe' as content,drugdate as ddate from drug where epid is null";
			sql+=" union ";
			sql+="select id as id,rolltitle as title,2 as dtype,rollpath as content,rolldate as ddate from rollnotice where rolltype=4 ";
			sql+=") as u order by ddate desc limit "+size*(page-1)+","+size*page;
			Session session=DAO.openSession();
			Connection con=session.connection();
			ResultSet rs=con.createStatement().executeQuery(sql);
			while(rs.next())
			{
				Drug d=new Drug();
				d.setType(rs.getString("dtype"));
				d.setDrugdate(rs.getDate("ddate"));
				d.setDrugname(rs.getString("title"));
				d.setDrupdescribe(rs.getString("content").toString());
				d.setId(rs.getInt("id"));
				list.add(d);
			}
			/*
			String sql="select *from rollnotice ";
			Session session=DAO.openSession();
			Connection con=session.connection();
			ResultSet rs=con.createStatement().executeQuery(sql);
			while(rs.next())
			{
				System.out.println(rs.getString("rollpath"));
			}
			*/
			
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
		}
		return list;
	}

	
}